﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Transactions;

namespace LogicUniversityBiz
{
    public class CoreHeadFascade
    {
        LogicUniversityEntities contextDB = new LogicUniversityEntities();

        public static EFFascade getEFFascade()
        {
            return new EFFascade();
        }

        //to retrieve all catelogue
        public List<Catelogue> getAllCatelogue()
        {
            var query = (from c in contextDB.Catelogues
                         select c);

            return query.ToList();
        }

        public List<string> getAllCategory()
        {
            var query = (from c in contextDB.Catelogues
                         select c.Category).Distinct();

            return query.ToList();
        }

        public List<Catelogue> getCatelogue(string category)
        {
            var query = from c in contextDB.Catelogues
                        where c.Category == category
                        select c;

            return query.ToList();
        }

        //to retrieve department code by empID
        public string getDepartmentCode(string empID)
        {
            var query = from emp in contextDB.Employees
                        where emp.Employee_ID == empID
                        select emp.Dept_Code;

            return query.Single();
        }



        public List<Department> getOutstandingDepartmentNames()
        {
            var query = (from r in contextDB.Retrieval_List
                         join d in contextDB.Departments on r.Dept_Code equals d.Dept_Code
                         select d).Distinct();

            return query.ToList();
        }

        public List<OutstandingItems> getOustandingItems(string deptCode)
        {

            var query = from r in contextDB.Retrieval_List
                        join c in contextDB.Catelogues on r.Item_Code equals c.Item_Code
                        where r.Dept_Code == deptCode
                        group r by new { c.Item_Code, c.Item_Description, c.Measurement } into g
                        select new OutstandingItems()
                        {
                            ItemCode = g.Key.Item_Code,
                            ItemDesc = g.Key.Item_Description,
                            Measurement = g.Key.Measurement,


                            //TotalRequested = (
                            //    from r2 in contextDB.Retrieval_List
                            //    join c2 in contextDB.Catelogues
                            //    on r2.Item_Code equals c2.Item_Code
                            //    select r2.Need_Quanty
                            //).Sum(),

                            TotalRequested = g.Sum(t1 => t1.Need_Quanty),

                            //TotalReceived = (
                            //    from r2 in contextDB.Retrieval_List
                            //    join c2 in contextDB.Catelogues
                            //    on r2.Item_Code equals c2.Item_Code
                            //    select r2.Actual_Quanty
                            //).Sum(),

                            TotalReceived = g.Sum(t2 => t2.Actual_Quanty),

                            //OutstandingQty = (
                            //    from r2 in contextDB.Retrieval_List
                            //    join c2 in contextDB.Catelogues
                            //    on r2.Item_Code equals c2.Item_Code
                            //    select r2.Need_Quanty
                            //).Sum() - (
                            //    from r2 in contextDB.Retrieval_List
                            //    join c2 in contextDB.Catelogues
                            //    on r2.Item_Code equals c2.Item_Code
                            //    select r2.Actual_Quanty
                            //).Sum()

                            OutstandingQty = g.Sum(t1 => t1.Need_Quanty) - g.Sum(t2 => t2.Actual_Quanty)
                        };
            //var query = from l in contextDB.Retrieval_List
            //            where l.Dept_Code == deptCode
            //            group new { l.Catelogue, l } by new
            //            {
            //                l.Catelogue.Item_Code,
            //                l.Catelogue.Item_Description,
            //                l.Dept_Code,
            //                l.Catelogue.Measurement
            //            } into g
            //            select new OutstandingItems()
            //            {
            //                g.Key.Item_Code,
            //                g.Key.Item_Description,
            //                Total_Requested_Qty = (System.Int32?)g.Sum(p => p.l.Need_Quanty),
            //                Total_Received_Qty = (System.Int32?)g.Sum(p => p.l.Actual_Quanty),
            //                Outstanding_Qty = (System.Int32?)(g.Sum(p => p.l.Need_Quanty) - g.Sum(p => p.l.Actual_Quanty)),
            //                g.Key.Measurement
            //            };

            return query.ToList();
        }

        public void insertRequisition(Requisition req)
        {


            contextDB.Requisitions.AddObject(req);
            contextDB.SaveChanges();

        }

        public void insertRequisitionDetail(Requisition_Detail reqDetail)
        {
            contextDB.Requisition_Detail.AddObject(reqDetail);
            contextDB.SaveChanges();
        }

        public int getLastRequisitionID()
        {
            var query = (from req in contextDB.Requisitions
                         select req.Requisition_ID).Max();

            return query;
        }

        //to show on MakeRequestDetail.aspx page
        public List<RequisitionWithItemDetails> getRequisitionItemsDetail(int reqID)
        {
            var query = from rd in contextDB.Requisition_Detail
                        join c in contextDB.Catelogues on rd.Item_Code equals c.Item_Code
                        where rd.Req_ID == reqID
                        select new RequisitionWithItemDetails()
                        {
                            ItemCode = c.Item_Code,
                            ItemDesc = c.Item_Description,
                            Quantity = rd.Quantity
                        };

            return query.ToList();
        }

        public List<RequisitionWithItemDetails> getTotalWeeklyRequisitions(DateTime fromDate, DateTime toDate, string deptCode)
        {
            var subquery = from r in contextDB.Requisitions
                           where r.Status.Equals("Approved") &&
                           (r.Approved_Date >= fromDate && r.Approved_Date <= toDate) &&
                           r.Dept_Code.Equals(deptCode)
                           select r.Requisition_ID;


            var query = from rd in contextDB.Requisition_Detail
                        join c in contextDB.Catelogues on rd.Item_Code equals c.Item_Code
                        where subquery.Contains((int)rd.Req_ID)
                        select new RequisitionWithItemDetails()
                        {
                            ItemCode = c.Item_Code,
                            ItemDesc = c.Item_Description,
                            Quantity = rd.Quantity
                        };

            return query.ToList();
        }

        public List<Catelogue> getCatelogueItemsUnderReorderLevel()
        {
            var query = (from c in contextDB.Catelogues
                         where c.Balance <= c.Reorder_Level
                         select c);

            return query.ToList();
        }

        public int getReorderQuantityOf(string itemCode)
        {
            var query = from c in contextDB.Catelogues
                        where c.Item_Code == itemCode
                        select c.Reorder_Qty;

            return query.Single();
        }

        public int getCurrentBalanceOf(string itemCode)
        {
            var query = from c in contextDB.Catelogues
                        where c.Item_Code == itemCode
                        select c.Balance;

            return query.Single();
        }

        public decimal getUnitPriceOf(string itemcode)
        {
            var query = from td in contextDB.Tender_Detail
                        where td.Item_Code == itemcode
                        select td.Unit_Price;

            return query.Single();
        }

        public List<string> getSuppliersNames()
        {
            var query = from s in contextDB.Suppliers
                        select s.Supplier_Name;

            return query.ToList();
        }

        public string getSupplierIDBy(string name)
        {
            var query = from s in contextDB.Suppliers
                        where s.Supplier_Name == name
                        select s.Supplier_Code;

            return query.Single();
        }

        public List<Catelogue> getStationeryItemsSuppliedBy(string code)
        {
            var query = from c in contextDB.Catelogues
                        join td in contextDB.Tender_Detail on c.Item_Code equals td.Item_Code
                        join t in contextDB.Tenders on td.Tender_ID equals t.Tender_ID
                        where t.Supplier_ID == code
                        select c;

            return query.ToList();
        }

        public void recorePurchaseOrder(Purchse_Order purchaseOrder)
        {
            using (TransactionScope trans = new TransactionScope())
            {
                contextDB.Purchse_Order.AddObject(purchaseOrder);

                foreach (Purchase_Order_Detail pd in purchaseOrder.Purchase_Order_Detail)
                {
                    contextDB.Purchase_Order_Detail.AddObject(pd);
                }

                contextDB.SaveChanges();
                trans.Complete();
            }
        }

        //wai hnin fascade
        public IQueryable getCollection(String coll)
        {
            var query = from c in contextDB.Departments
                        where c.Collection_Point == coll
                        select new { c.Collection_Point };
            return query;
        }
        public IQueryable getcollectionpoint()
        {
            var query = from dept in contextDB.Departments
                        select dept;
            return query;
        }
        //to retrieve Representative
        public IQueryable getRepresentative(String rep)
        {
            var query = from c in contextDB.Employees
                        where c.Employee_Name == rep
                        select new { c.Employee_Name };
            return query;
        }
        public IQueryable getRepresentativeName(string deptCode)
        {
            var query = from emp in contextDB.Employees
                        where emp.Dept_Code==deptCode & emp.Role!="Department Head"
                        select emp;
            return query;
        }
        public void getRepCollection(string dept, string empname,string deptCode)
        {
            /* var query = from dep in contextDB.Departments
                         join emp in contextDB.Employees on dep.Dept_Code equals emp.Dept_Code
                         where dep.Dept_Code ==dept && emp.Employee_Name ==empname 
                         select new { dep.Dept_Name, emp.Employee_Name };
             return query;
             */
           // Department dep = contextDB.Departments.First<Department>(x => x.Dept_Code == deptCode);
            Department dep = (from i in contextDB.Departments
                              where i.Dept_Code == deptCode
                              select i).SingleOrDefault();
            
            dep.Collection_Point = dept;
            dep.Rep_Name = empname;
            this.contextDB.SaveChanges();


        }
        public IQueryable getShowCollection(string dept, string empname)
        {
            var query = from dep in contextDB.Departments
                        join emp in contextDB.Employees on dep.Dept_Code equals emp.Dept_Code
                        where dep.Dept_Code == dept && emp.Employee_Name == empname
                        select new { dep.Dept_Name, emp.Employee_Name };
            return query;
        }

        //to retrieve requisiton
        //CurrentTime.Text = DateTime.Now.ToString();
        //CurrentTime2.Text = DateTime.Now.AddDays(-7).ToShortDateString();
        public IQueryable getRequisiton(DateTime currentDate, DateTime previousDate,string code)
        {
            //SELECT DISTINCT rid.itemId, i.itemDescription,SUM(rid.requestQuantity + rid.outstandingQuantity) AS TotalRequest, 
            //    i.balance FROM dbo.Item AS i INNER JOIN dbo.RequestItemByDept AS rid ON 
            //    i.itemId = rid.itemId INNER JOIN dbo.RequestByDept AS rd ON rid.requestId = rd.requestId 
            //WHERE (rd.Date > DATEADD(day, - 7, GETDATE())) AND (rd.statusOfAssigning = 'pending' OR rd.statusOfAssigning = 'outstanding') 
            //GROUP BY rid.itemId, i.balance, i.itemDescription

            var query = from req in contextDB.Requisitions
                        join emp in contextDB.Employees on req.Emp_ID equals emp.Employee_ID
                        where (req.Requested_Date >= currentDate && req.Requested_Date <= previousDate) &&
                        req.Emp_ID==code
                        select new {req.Requisition_ID, emp.Employee_Name, req.Requested_Date, req.Status,req.Reason };
            return query;

        }

        public List<EmployeeRequests> getEmployeeRequests(string deptCode)
        {
            var query = (from req in contextDB.Requisitions
                         join emp in contextDB.Employees
                         on req.Emp_ID equals emp.Employee_ID
                         where req.Status == "Pending" && 
                         req.Dept_Code==deptCode
                         select new EmployeeRequests()
                         {
                             RequestID=req.Requisition_ID,
                             EmployeeID = emp.Employee_ID,
                             EmployeeName = emp.Employee_Name,
                             RequestedDate = req.Requested_Date
                         }).OrderByDescending(x=>x.RequestedDate);

            return query.ToList();
        }

        public IQueryable selectcollectionpoint()//select  collection point
        {
            var query = from m in contextDB.Departments
                        select m;
            return query;


        }

        public void savecollectionpoint(string deptcode, string newcollectionpoint)
        {
            Department query = (from m in contextDB.Departments
                                where m.Dept_Code == deptcode
                                select m).FirstOrDefault();

            query.Collection_Point = newcollectionpoint;
            this.contextDB.SaveChanges();

        }

        public Requisition updateStatus(int id)
        {
            var query = (from r in contextDB.Requisitions
                         where r.Requisition_ID == id
                         select r).FirstOrDefault();
            query.Status = "Approved";

            return query;
        }
        //to retrive all requisitions
        public IQueryable getAllRequisition()
        {
            var query = from ep in contextDB.Employees
                        join re in contextDB.Requisitions
                        on ep.Employee_ID equals re.Emp_ID
                        select new
                        {
                            Employee_Name = ep.Employee_Name,
                            Requested_Date = re.Requested_Date
                        };

            return query;
        }

        //to retrieve all employee list
        public IQueryable getAllEmployee()
        {
            var query = from ep in contextDB.Employees
                        select new
                        {
                            Employee_Name = ep.Employee_Name
                        };
            return query;



        }

        public IQueryable getEmployeeRequestsDetails(int reqID)
        {
            var query = from rd in contextDB.Requisition_Detail
                        join c in contextDB.Catelogues
                        on rd.Item_Code equals c.Item_Code
                        where rd.Req_ID == reqID
                        select new
                        {
                            rd.Item_Code,
                            c.Item_Description,
                            rd.Quantity
                        };

            return query;
        }

        public void updateApprove(int reqID)
        {
            var query = (from r in contextDB.Requisitions
                        where r.Requisition_ID == reqID
                        select r).SingleOrDefault();

            query.Status = "Approved";
            query.Approved_Date = System.DateTime.Today;

            contextDB.SaveChanges();
        }

        public void updateReject(int reqID,string reason)
        {
            var query = (from r in contextDB.Requisitions
                         where r.Requisition_ID == reqID
                         select r).SingleOrDefault();

            query.Status = "Rejected";
            query.Reason = reason;
            query.Approved_Date = System.DateTime.Today;

            contextDB.SaveChanges();
        }

        public List<Employee> getAllEmployeeUnder(string deptCode)
        {
            var query = from e in contextDB.Employees
                        where e.Dept_Code == deptCode && e.Role != "Department Head"
                        select e;

            return query.ToList();
        }

        public void delegateHead(string empID)
        {
            var query = (from emp in contextDB.Employees
                        where emp.Employee_ID == empID
                        select emp).SingleOrDefault();

            query.Temp_Role = "Department Head";
            contextDB.SaveChanges();
        }

        public void undelegateHead(string empID)
        {
            var query = (from emp in contextDB.Employees
                         where emp.Employee_ID == empID
                         select emp).SingleOrDefault();

            query.Temp_Role = null;
            contextDB.SaveChanges();
        }
        public Requisition getRequision(int id)
        {
            var query = from r in contextDB.Requisitions
                        where r.Requisition_ID == id
                        select r;
            return query.SingleOrDefault();
        }
        public List<Requisition_Detail> getReqDetail(int id)
        {
            var query = from r in contextDB.Requisition_Detail
                        where r.Req_ID == id
                        select r;
            return query.ToList();
        }
    }
}
